﻿CREATE PROCEDURE [ServiceEngine].[GetOperationVersionsForEndpointAndFirstBodyElement]
	@EndpointID uniqueidentifier,
	@ElementName nvarchar(4000),
	@namespaceURI nvarchar(4000)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SchemaID uniqueidentifier

	IF @ElementName is null  -- Blank Request Msg
	BEGIN
		SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
				OV.AutoResponse, OV.IsPublic, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
				,OV.OperationID, OV.ResourceID, OV.PolicyID
		FROM	[ServiceEngine].OperationVersions OV
		JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
		JOIN	[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID		
		WHERE	AO.EndpointID = @EndpointID
		AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
		AND NOT EXISTS (
			SELECT 1
			FROM	[ServiceEngine].MessageParts MP
			WHERE	MP.OperationVersionID = OV.OperationVersionID
			AND		MP.IsRequestMsg = 1)
		ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
	END
	ELSE
	BEGIN
		SELECT  @SchemaID = N.EntityID
		FROM	[ServiceEngine].Names N
		WHERE	N.EntityTypeID = 9
		AND		N.EntityName = @namespaceURI

		SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
				OV.AutoResponse, OV.IsPublic, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
				,OV.OperationID, OV.ResourceID, OV.PolicyID
		FROM	[ServiceEngine].OperationVersions OV
		JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
		JOIN	[ServiceEngine].MessageParts MP on MP.OperationVersionID = OV.OperationVersionID
		JOIN	[ServiceEngine].SchemaObjects SO on SO.SchemaObjectID = MP.SchemaObjectID
		JOIN	[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID		
		WHERE	AO.EndpointID = @EndpointID
		AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
		AND		MP.IsRequestMsg = 1
		AND		MP.Ordinal = 0
		AND		SO.SchemaID = @SchemaID
		AND		SO.SchemaObjectName = @ElementName
		AND		SO.DateRevised = NULL -- This is for the latest node
		ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
	END
END


